impdp ORA |
您所在的位置：网站首页 › ora-00001: 违反唯一约束条件pk › impdp ORA |
|
impdp system/******** directory=DUMP_DIR_TEST dumpfile=czyjpxdb1113.dmp logfile=czyjpxdb1113.log schemas=mgrdb,accdb,txndb,comdb,czyjdb,rpt,stldb remap_tablespace=TBS_CZKDATA:TBS_CZYJDATA1 报错： ORA-39083: Object type JOB failed to create with error: ORA-00001: unique constraint (SYS.I_JOB_JOB) violated Failing sql is: BEGIN SYS.DBMS_IJOB.SUBMIT( JOB=> 23, LUSER=> 'CZYJDB', PUSER=> 'CZYJDB', CUSER=> 'CZYJDB', NEXT_DATE=> TO_DATE('2014-11-13 22:03:45', 'YYYY-MM-DD:HH24:MI:SS'), INTERVAL=> 'SYSDATE + 120/1440 ', BROKEN=> FALSE, WHAT=> 'dbms_refresh.refresh(''"CZYJDB"."MV_TBL_CARD_ACCOUNT"'');', NLSENV=> 'NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$'' NLS_ISO_CURRENCY=''AMER Processing object type SCHEMA_EXPORT/REFRESH_GROUP Job "SYSTEM"."SYS_IMPORT_SCHEMA_02" completed with 4 error(s) at Fri Nov 14 09:00:27 2014 elapsed 0 00:30:37 报错说明：该job已经存在 select job, what from dba_jobs; JOB WHAT ----- ---------------------------------------------------------------------------------------------------- 4001 wwv_flow_cache.purge_sessions(p_purge_sess_older_then_hrs => 24); 4002 wwv_flow_mail.push_queue(wwv_flow_platform.get_preference('SMTP_HOST_ADDRESS'),wwv_flow_platform.get _preference('SMTP_HOST_PORT')); 23 dbms_refresh.refresh('"CZYJDB"."MV_TBL_CARD_ACCOUNT"'); 发现23号job确实存在 解决方法： 手动创建一个未使用过的job号来使用 declare my_job number; begin select max (job) + 1 into my_job from dba_jobs; dbms_job.isubmit (job => my_job, next_date => to_date ('2014-11-15 00:00:00', 'YYYY-MM-DD:HH24:MI:SS'), interval => 'trunc (sysdate + 1)', what => 'create_MV_TBL_CARD_ACCOUNT;', no_parse => true); end; / 再次执行impdp，成功！ 参考文档： DataPump Import (IMPDP) Errors ORA-39083 ORA-00001 Unique Constraint SYS.I_JOB_JOB Violated (Doc ID 783299.1) APPLIES TO: Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.4 [Release 10.1 to 11.2] Information in this document applies to any platform. ***Checked for relevance on 28-May-2014*** SYMPTOMS You receive the following errors when performing DataPump import: ORA-39083: Object type JOB failed to create with error: ORA-00001: unique constraint (SYS.I_JOB_JOB) violated Failing sql is: BEGIN DBMS_JOB.ISUBMIT (JOB => 61, NEXT_DATE => TO_DATE ('2009-02-16 00:00:00', 'YYYY-MM-DD:HH24:MI:SS'), INTERVAL => 'TRUNC (SYSDATE + 1)', WHAT => 'Delete_TS_BLOBS;', NO_PARSE => TRUE); END; Job "SYSTEM"."SYS_IMPORT_FULL_02" completed with 1 error(s) at 15:14:38 CAUSE The job with ID 61 already exists in the target database. The job ID (JOBID) must always be unique (two jobs with same ID are not allowed). Verify the job existence: select job, what from dba_jobs; JOB WHAT ----- -------- 61 AGILE9_WEEKLY_ANALYZE NOTE: If a job is already running, then it may not show up in view USER_JOBS, if another user started the job. You have to check the view DBA_JOBS to verify the job ID is not in use. SOLUTION 1. Manually create the failed job with a different job ID. For example: declare m_job number; begin select max (job) + 1 into m_job from dba_jobs; dbms_job.isubmit (job => m_job, next_date => to_date ('2009-02-16 00:00:00', 'YYYY-MM-DD:HH24:MI:SS'), interval => 'trunc (sysdate + 1)', what => 'Delete_TS_BLOBS;', no_parse => true); end; / - OR - 2. Remove the existing job with same job ID (61) (only if the job is not required) and then perform the import: connect / as sysdba exec dbms_job.remove (61); exit REFERENCES |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |